IF OBJECT_ID('dbo.RptCSCRApprovedAgreementDetail') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRApprovedAgreementDetail
    IF OBJECT_ID('dbo.RptCSCRApprovedAgreementDetail') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRApprovedAgreementDetail >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRApprovedAgreementDetail >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRApprovedAgreementDetail
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 04/08/2008
--
-- Description: Detail List for Report, "CSCRApprovedAgreementSummary"
--                  Used for User Testing
--
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRApprovedAgreementDetail()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 04/08/08    KC        Created
-- 04/14/08    KC        Changed for new View on Master/Amendments
-- 04/16/08    KC        Changed CreateDate to PendingDate
-- 04/17/08    KC        Added table for performance
-- 05/13/08    KC        Added @IncludeDanaFarber, Changed logic to count
--                       Deliverables
-- 05/15/08    KC        Remmoved DelStatus "_61" as it is Pending!
-- 05/21/08    KC        Fixed DanaFarber to use Function.
-- 05/22/08    KC        DFCPP changed to DFPCC
-- 06/09/08    KC        Finalized with feedback from Report Spec.
-- 06/12/08    KC        CaseManager=0 for Unassigned not working properly.
--                       Fix is in.
-- 06/16/08    KC        Increased size of @Institution, not handling DFCI 
--                       properly. Added PrincipalInvestigator to Result
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRApprovedAgreementDetail
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(20)='ALL',
     @CSCRManagerId                     INT=0,
     @SubActivityTypeGroup              VARCHAR(20)='TotalCount',
     @IncludeDanaFarber                 VARCHAR(3) = NULL,   
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ConversionDate1    DATETIME,
               @ConversionDate2    DATETIME,
               @Institutions       VARCHAR(50)
              
     -- Default this, Records with these PropStatDate(s) will not be returned
     SET  @ConversionDate1 = '8/3/2007'
     SET  @ConversionDate2 = '8/4/2007'
     
     -- ALL checking
     IF @Institution IN ('ALL','0','',NULL) SET @Institution = NULL
     IF @CSCRManagerId IN (0,NULL) SET @CSCRManagerId = NULL
     
     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institutions = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institutions = 'BWH,MGH,NWH,SRH'
     
     -- Need a temp table because last WHERE clause takes 3 mins to complete!
     -- Build table so Proc can fill it.
     DECLARE  @Agreements TABLE (
                    MasterFolderNumber            VARCHAR(12),
                    ReportId                      INT,
                    CSCRCompletedDate             DATETIME,
                    CSCRManagerId                 INT,
                    CSCRCaseManagerName           VARCHAR(50),
                    Institution                   VARCHAR(50),
                    InfoEdPropNumber              VARCHAR(50),
                    ProposalShortTitle            VARCHAR(64),
                    MasterProposalStatus          VARCHAR(40),
                    OverallStatus                 VARCHAR(50),
                    ProposalStatus                VARCHAR(50),
                    ActivityDescription           VARCHAR(128),
                    SubActivityType               VARCHAR(60),
                    SubActivityGroup              VARCHAR(10),
                    SubActivityGroupSearch        VARCHAR(12),
                    DeliverableType               VARCHAR(30),
                    DeliverableReportName         VARCHAR(50),
                    PrincipalInvestigator         VARCHAR(64)
                    )
          
     -- Get List of Agreements
     INSERT    @Agreements
               (MasterFolderNumber,     
               ReportId,           
               CSCRCompletedDate,            
               CSCRManagerId,          
               CSCRCaseManagerName,    
               Institution,            
               InfoEdPropNumber,       
               ProposalShortTitle,     
               MasterProposalStatus,   
               OverallStatus,          
               ProposalStatus,         
               ActivityDescription,    
               SubActivityType,        
               SubActivityGroup,       
               SubActivityGroupSearch,
               DeliverableType,
               DeliverableReportName,
               PrincipalInvestigator)
     SELECT    AI.FolderNumber AS MasterFolderNumber,
               DELS.ReportId AS ReportId,
               dbo.fnRemoveTime(DELS.CSCRCompletedDate) AS CSCRCompletedDate,
               ISNULL(AI.CSCRManagerId,-1),
               ISNULL(AI.CSCRCaseManagerName,'Unassigned'),
               AI.Institution,
               AI.InfoEdPropNumber,
               AI.ProposalShortTitle,
               AI.MasterProposalStatus,
               AI.OverallStatus,
               AI.ProposalStatus,
               AI.ActivityDescription,
               AI.SubActivityType,
               CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'CTA'
                    WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDA'
                    ELSE 'OTHER'
               END AS SubActivityGroup,
               CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'CTACount'
                    WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDACount'
                    ELSE 'OTHERCount'
               END AS SubActivityGroupSearch,
               DELS.REPORTCAT AS DeliverableType,
               DELS.ReportName AS DeliverableReportName,
               AI.PrincipalInvestigator AS PrincipalInvestigator
     FROM      dbo.AgrAgreement_Info AI INNER JOIN
              
               -- New logic. Count Deliverables, they they assume Activity codes of Parent Agreement
               (SELECT  D.prop_no,
                       D.ReportId,
                       D.ReportName,
                       D.REPORTCAT,
                       MIN(D.ProcessedDate) AS CSCRCompletedDate
               FROM    dbo.vwInfoEdCSCRDeliverables D
               WHERE   D.REPORTCAT_CODE IN ('REPORTCAT_19',    --  CSCR - Contract
                                            'REPORTCAT_32',    --     CSCR - Agreement
                                            'REPORTCAT_33',    --     CSCR - Amendment
                                            'REPORTCAT_34',    --     CSCR - Subcontract
                                            'REPORTCAT_36')    --     CSCR - Sub-Amendment
                       AND D.REPORTSTAT_CODE = 'REPORTSTAT_13' -- CSCR - Completed
               GROUP BY D.prop_no,
                       D.ReportId,
                       D.ReportName,
                       D.REPORTCAT ) DELS ON AI.InfoEdPropNumber = DELS.prop_no

     WHERE     -- This is the Where clause from the Parent Report...
               (dbo.fnRemoveTime(DELS.CSCRCompletedDate) BETWEEN @StartDate AND @EndDate ) AND    

               (RTRIM(AI.ActivityDescription) = 'Clinical Research') AND
                 
               (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution)) 
               OR (@Institution IS NULL AND AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institutions))))

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return ResultSet
     SELECT    MasterFolderNumber,     
               ReportId,           
               CSCRCompletedDate,            
               CSCRManagerId,          
               CSCRCaseManagerName,    
               Institution,            
               InfoEdPropNumber,       
               ProposalShortTitle,     
               MasterProposalStatus,   
               OverallStatus,          
               ProposalStatus,         
               ActivityDescription,    
               SubActivityType,        
               SubActivityGroup,
               DeliverableType,
               DeliverableReportName,
               PrincipalInvestigator
     FROM      @Agreements A
     -- Which Column?
     WHERE     -- These are additional Criteria to provide drilldown to this report
               -- (based on which number the user clicked on in the ParentReport
               (@SubActivityTypeGroup = 'TotalCount' OR
               @SubActivityTypeGroup = A.SubActivityGroupSearch)  AND
               
               (@CSCRManagerId IS NULL OR A.CSCRManagerId = @CSCRManagerId) 
     ORDER BY A.CSCRCompletedDate                         
                    
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptCSCRApprovedAgreementDetail') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRApprovedAgreementDetail >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRApprovedAgreementDetail >>>'
go

GRANT EXECUTE ON dbo.RptCSCRApprovedAgreementDetail TO InsightReport,Insight
go

